﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using MySql.Data.MySqlClient;
using System.Threading.Tasks;
using Models;
using Utility;
using Service.Interface;
using Repository.Interface;
using SqlSugar;

namespace Service.Impl
{
    public class ReportServices : IReportServices
    {
        private IBaseRepository _PackinforRepository;
        private SqlSugarClient _db;
        public ReportServices(ISqlSugarClient db, IBaseRepository PackinforRepository)
        {
            _db = db as SqlSugarClient;
            _PackinforRepository = PackinforRepository;
        }

        public List<LaborDetail> GetProcesReportData()
        {
            var odata = _db.GetConnection("OUSB").Ado.GetDataTable(Strsql("oracle"));
            var mdata = _db.GetConnection("SAE_OFN").Ado.GetDataTable(Strsql("mysql")); 
            odata.Merge(mdata);
            return GeneralMethod.DataToList<LaborDetail>(odata);
        }

        public static string Strsql(string type)
        {
            string strsql = $@"
            SELECT
                {(type.ToLower().Equals("mysql") ? "CONVERT(TRANSACTION_SEQ,CHAR) AS TRANSACTION_SEQ," : "TRIM(CAST(TRANSACTION_SEQ AS CHAR(10))) AS TRANSACTION_SEQ,")}
                {(type.ToLower().Equals("mysql") ? "CONVERT(TRANSACTION_SEQ,CHAR) AS PROACTIVIKEY_C," : "TRIM(CAST(TRANSACTION_SEQ AS CHAR(10))) AS PROACTIVIKEY_C,")}
                COMPANY,
                JOBNUM,
                ASSEMBLYPARTNUM,
                PROCESSNAME,
                LABORQTY,
                SCRAPQTY,
                DISCREPQTY,
                SCRAPREASONCODE,
                DISCRPRSNCODE,
                PAYROLLDATE,
                LABORNOTE,
                EMPLOYEENUM,
                PRODUCTIONLOT_C,
                CREATE_TIME,
	            '{type}' AS TYPE
            FROM
	            LABOR_DETAIL
            WHERE
				STATUS='N' 
                /* 
                AND ROWNUM < 10 
                AND CREATE_TIME >= {(type.ToLower() == "mysql" ? $"{DateTime.Now.AddDays(-7).ToString("yyy-MM-dd")}" : $"to_date('{DateTime.Now.AddDays(-7).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')")} 
                AND CREATE_TIME <= {(type.ToLower() == "mysql" ? $"{DateTime.Now.AddDays(1).ToString("yyy-MM-dd")}" : $"to_date('{DateTime.Now.AddDays(1).ToString("yyy-MM-dd")}', 'yyyy-mm-dd')")} 
                */
            ORDER BY
	            CREATE_TIME ASC ";
            return strsql.Replace(@"[\r\n]", "");
        }

        /*
        public void  Insert()
        {
            for (int i = 0; i < 10000; i++)
            {
                string sql = $@"
                INSERT INTO LABOR_DETAIL  
	                (
		                COMPANY,
		                JOBNUM,
		                ASSEMBLYPARTNUM,
		                PROCESSNAME,
		                PAYROLLDATE,
		                PRODUCTIONLOT_C,
		                EMPLOYEENUM,
		                STATUS,
		                CREATE_TIME,
		                LABORQTY,
		                SCRAPQTY,
		                DISCREPQTY,
		                SCRAPREASONCODE,
		                DISCRPRSNCODE,
		                LABORNOTE 
	                ) 
	            SELECT
	                COMPANY,
	                'MP202305270001' AS JOBNUM,
	                ASSEMBLYPARTNUM,
	                PROCESSNAME,
	                PAYROLLDATE,
	                PRODUCTIONLOT_C,
	                EMPLOYEENUM,
	                'N' AS STATUS,
	                CREATE_TIME,
	                LABORQTY,
	                SCRAPQTY,
	                DISCREPQTY,
	                SCRAPREASONCODE,
	                DISCRPRSNCODE,
	                LABORNOTE 
                FROM
	                LABOR_DETAIL 
                WHERE
	                JOBNUM = 'MP202304190014'";
                FOracleHelper.ExecuteNonQuery(sql.Replace(@"[\r\n]", ""));
            }
        }*/

        public Task<bool> UpdateProcesReport(List<LaborDetail> list)
        {
            int Count = 0;
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].type.Trim().ToUpper().Equals("ORACLE"))
                {
                    List<OracleParameter> parms = new List<OracleParameter>()
                    {
                        new OracleParameter(":jobnum",OracleType.VarChar){ Value=list[i].jobnum.ToString()},
                        new OracleParameter(":processname",OracleType.VarChar){ Value=list[i].processname.ToString()},
                        new OracleParameter(":productionlot_c",OracleType.VarChar){ Value=list[i].productionlot_c.ToString()},
                        new OracleParameter(":payrolldte",OracleType.DateTime){ Value=list[i].payrolldate.ToString()},
                        new OracleParameter(":create_time",OracleType.DateTime){ Value=list[i].create_time.ToString()},
                        new OracleParameter(":status",OracleType.NVarChar){ Value=list[i].status.ToString()},
                        new OracleParameter(":transaction_seq",OracleType.Number){ Value=list[i].transaction_seq.ToString()},
                        new OracleParameter(":return_message",OracleType.NVarChar){ Value=list[i].return_message.ToString()}
                    };
                    string oraclestr = $@" 
                    UPDATE LABOR_DETAIL SET STATUS=:status ,RETURN_MESSAGE=:return_message
                    WHERE  STATUS ='N' AND TRANSACTION_SEQ=:transaction_seq AND JOBNUM=:jobnum AND PROCESSNAME=:processname AND PRODUCTIONLOT_C =:productionlot_c AND PAYROLLDATE =:payrolldte AND CREATE_TIME=:create_time ";
                    Count += _db.GetConnection("OUSB").Ado.ExecuteCommand(oraclestr.Replace(@"[\r\n]", ""), parms.ToArray());
                }
                if (list[i].type.Trim().ToUpper().Equals("MYSQL"))
                {
                    List<MySqlParameter> parms = new List<MySqlParameter>()
                    {
                        new MySqlParameter("@jobnum",MySqlDbType.VarChar){ Value=list[i].jobnum.ToString()},
                        new MySqlParameter("@processname",MySqlDbType.VarChar){ Value=list[i].processname.ToString()},
                        new MySqlParameter("@productionlot_c",MySqlDbType.VarChar){ Value=list[i].productionlot_c.ToString()},
                        new MySqlParameter("@payrolldte",MySqlDbType.DateTime){ Value=list[i].payrolldate.ToString()},
                        new MySqlParameter("@create_time",MySqlDbType.DateTime){ Value=list[i].create_time.ToString()},
                        new MySqlParameter("@status",MySqlDbType.VarChar){ Value=list[i].status.ToString()},
                        new MySqlParameter("@transaction_seq",MySqlDbType.Int32){ Value=list[i].transaction_seq.ToString()},
                        new MySqlParameter("@return_message",MySqlDbType.VarChar){ Value=list[i].return_message.ToString()}
                    };
                    string mysqlstr = $@"
                    UPDATE LABOR_DETAIL SET STATUS =@status ,RETURN_MESSAGE=@return_message
                    WHERE  STATUS ='N' AND TRANSACTION_SEQ=@transaction_seq AND JOBNUM=@jobnum AND PROCESSNAME=@processname AND PRODUCTIONLOT_C=@productionlot_c AND PAYROLLDATE=@payrolldte AND CREATE_TIME=@create_time ";
                    Count += _db.GetConnection("SAE_OFN").Ado.ExecuteCommand(mysqlstr.Replace(@"[\r\n]", ""), parms.ToArray());
                }
            }
            return Task.FromResult(list.Count == Count ? true : false);
        }

        /// <summary>
        /// 同步MySQL不良数据 
        /// </summary>
        /// <returns></returns>
        public bool ProductFail()
        {
            bool status = false;
            //数据源
            string mysqlstr = @"
            SELECT  PRODUCT,
				    PRODUCTION_LOT_NO,
				    PROCESS_NAME,
				    DATE_FORMAT(INPUT_TIME,'%Y-%m-%d %H:%i:%s')AS INPUT_TIME, 
				    KEY1, KEY2, KEY3, INFO1, INFO2, INFO3, INFO4, TTL_QTY, FAIL_QTY, 
                    DEFECT1_QTY, DEFECT2_QTY, DEFECT3_QTY, DEFECT4_QTY, DEFECT5_QTY, DEFECT6_QTY, DEFECT7_QTY, DEFECT8_QTY, DEFECT9_QTY, DEFECT10_QTY, 
                    DEFECT11_QTY, DEFECT12_QTY, DEFECT13_QTY, DEFECT14_QTY, DEFECT15_QTY, DEFECT16_QTY, DEFECT17_QTY, DEFECT18_QTY, DEFECT19_QTY, DEFECT20_QTY, 
                    DEFECT21_QTY, DEFECT22_QTY, DEFECT23_QTY, DEFECT24_QTY, DEFECT25_QTY, DEFECT26_QTY, DEFECT27_QTY, DEFECT28_QTY, DEFECT29_QTY, DEFECT30_QTY, 
                    DEFECT31_QTY, DEFECT32_QTY, DEFECT33_QTY, DEFECT34_QTY, DEFECT35_QTY, DEFECT36_QTY, DEFECT37_QTY, DEFECT38_QTY, DEFECT39_QTY, DEFECT40_QTY, 
                    DEFECT41_QTY, DEFECT42_QTY, DEFECT43_QTY,CREATE_USER, MODIFY_USER, MARKING, PART_NO, JOB_NUM,LOT_OPERATOR,JOBNUMT		
            FROM
		            (
                        SELECT VAL.*,
                               PL.PRODUCTION_LOT_NO,
                               PL.PART_NO,
                               PL.JOB_NUM,
                               LOT.LOT_OPERATOR,
                               LAB.JOBNUM AS JOBNUMT
                        FROM T_DEFECT_KEY_INFO AS KE
                        INNER JOIN DEFECT_PROD AS VAL
                            ON KE.PRODUCT = VAL.PRODUCT
                                AND KE.PROCESS_NAME = VAL.PROCESS_NAME 
                                AND KE.INPUT_TIME = VAL.INPUT_TIME
                                AND KE.KEY1 = VAL.KEY1
                                AND KE.KEY2 = VAL.KEY2
                                AND KE.KEY3 = VAL.KEY3
                        INNER JOIN PRODUCTION_LOT_MASTER AS PL
                            ON PL.PRODUCT = KE.PRODUCT
                                AND PL.PRODUCTION_LOT_NO = KE.KEY1
                        LEFT JOIN PRODUCTION_LOT_MASTER LOT
                            ON LOT.PRODUCTION_LOT_NO = PL.PRODUCTION_LOT_NO
                                AND LOT.JOB_NUM = PL.JOB_NUM
                        LEFT JOIN LABOR_DETAIL LAB
                            ON LAB.JOBNUM = PL.JOB_NUM
                                AND LAB.ASSEMBLYPARTNUM = PL.PART_NO
                                AND LAB.PRODUCTIONLOT_C = PL.PRODUCTION_LOT_NO
                                AND LAB.CREATE_TIME = VAL.INPUT_TIME
                                AND LAB.EMPLOYEENUM = LOT.LOT_OPERATOR
                        WHERE KE.STATUS = 0
                                AND VAL.FAIL_QTY <> 0
                                AND LAB.JOBNUM IS NULL
		            )T;";

            DataTable data = _db.GetConnection("SAE_OFN").Ado.GetDataTable(mysqlstr);
            if (data.Rows.Count > 0)
            {
                //Mysql多列转多行
                DataTable newdt = new DataTable();
                newdt.Columns.Add("PRODUCT");
                newdt.Columns.Add("PRODUCTION_LOT_NO");
                newdt.Columns.Add("PROCESS_NAME");
                newdt.Columns.Add("INPUT_TIME");
                newdt.Columns.Add("KEY1");
                newdt.Columns.Add("KEY2");
                newdt.Columns.Add("KEY3");
                newdt.Columns.Add("DEFECT_KEY");
                newdt.Columns.Add("DEFECT_VALUE");
                newdt.Columns.Add("PART_NO");
                newdt.Columns.Add("JOB_NUM");
                newdt.Columns.Add("LOT_OPERATOR");
                newdt.Columns.Add("DISPLAY_NAME");
                //where
                int index = 0;
                string product = string.Empty;
                string processname = string.Empty;
                string defectkey = string.Empty;
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    product = $"{product}'{data.Rows[i]["PRODUCT"].ToString()}'{((data.Rows.Count - 1 > i) ? "," : "")}";
                    processname = $"{processname}'{data.Rows[i]["PROCESS_NAME"].ToString()}'{((data.Rows.Count - 1 > i) ? "," : "")}";
                    for (int c = 0; c < data.Columns.Count; c++)
                    {
                        if (data.Columns[c].ColumnName.ToUpper().Contains($"DEFECT".ToUpper()) && data.Columns[c].ColumnName.ToUpper().Contains($"_QTY".ToUpper()))
                        {
                            var defectvalue = data.Rows[i][data.Columns[c].ColumnName] == DBNull.Value ? 0 : data.Rows[i][data.Columns[c].ColumnName];
                            if (Convert.ToInt32(defectvalue) > 0)
                            {
                                defectkey = $"{defectkey}'{data.Columns[c].ColumnName.ToString()}'{(c > 0 && (data.Columns.Count - 1 > c) ? "," : "")}";
                                newdt.Rows.Add();
                                newdt.Rows[index]["PRODUCT"] = data.Rows[i]["PRODUCT"];
                                newdt.Rows[index]["PRODUCTION_LOT_NO"] = data.Rows[i]["PRODUCTION_LOT_NO"];
                                newdt.Rows[index]["PROCESS_NAME"] = data.Rows[i]["PROCESS_NAME"];
                                newdt.Rows[index]["INPUT_TIME"] = data.Rows[i]["INPUT_TIME"];
                                newdt.Rows[index]["KEY1"] = data.Rows[i]["KEY1"];
                                newdt.Rows[index]["KEY2"] = data.Rows[i]["KEY2"];
                                newdt.Rows[index]["KEY3"] = data.Rows[i]["KEY3"];
                                newdt.Rows[index]["DEFECT_KEY"] = data.Columns[c].ColumnName;
                                newdt.Rows[index]["DEFECT_VALUE"] = defectvalue;
                                newdt.Rows[index]["PART_NO"] = data.Rows[i]["PART_NO"];
                                newdt.Rows[index]["JOB_NUM"] = data.Rows[i]["JOB_NUM"];
                                newdt.Rows[index]["LOT_OPERATOR"] = data.Rows[i]["LOT_OPERATOR"];
                                index++;
                            }
                        }
                    }
                }

                //不良信息
                string sql = $@"
                SELECT
                    PRODUCT,
                    CATEGORY,
                    FIELD_NAME,
	                DISPLAY_NAME 
                FROM
	                DATA_DICTIONARY 
                WHERE
	                PRODUCT IN({product.TrimEnd(',')}) 
	                AND CATEGORY IN ({processname.TrimEnd(',')}) 
	                AND FIELD_NAME IN ({defectkey.TrimEnd(',')});";
                DataTable dict = _db.GetConnection("SAE_OFN").Ado.GetDataTable(sql.Replace(@"[\r\n]", ""));
                for (int a = 0; a < newdt.Rows.Count; a++)
                {
                    for (int b = 0; b < dict.Rows.Count; b++)
                    {
                        if (newdt.Rows[a]["PRODUCT"].ToString().Trim() == dict.Rows[b]["PRODUCT"].ToString().Trim() 
                            && newdt.Rows[a]["PROCESS_NAME"].ToString().Trim() == dict.Rows[b]["CATEGORY"].ToString().Trim()
                            && newdt.Rows[a]["DEFECT_KEY"].ToString().Trim() == dict.Rows[b]["FIELD_NAME"].ToString().Trim())
                        {
                            newdt.Rows[a]["DISPLAY_NAME"] = dict.Rows[b]["DISPLAY_NAME"];
                        }
                    }
                }

                var list = (from dt in newdt.AsEnumerable() 
                            select new 
                            {
                                product = dt.Field<string>("PRODUCT"),
                                production_lot = dt.Field<string>("PRODUCTION_LOT_NO"),
                                process_name = dt.Field<string>("PROCESS_NAME"),
                                input_time = dt.Field<string>("INPUT_TIME"),
                                key1 = dt.Field<string>("KEY1"),
                                key2 = dt.Field<string>("KEY2"),
                                key3 = dt.Field<string>("KEY3"),
                                defect_key = dt.Field<string>("DEFECT_KEY"),
                                defect_value = dt.Field<string>("DEFECT_VALUE"),
                                part_no = dt.Field<string>("PART_NO"),
                                job_num = dt.Field<string>("JOB_NUM"),
                                lot_operator = dt.Field<string>("LOT_OPERATOR"),
                                display_name = dt.Field<string>("DISPLAY_NAME")
                            }).ToList();
                string vaule = string.Empty;
                for (int i = 0; i < list.Count; i++)
                {
                    //var scrapType = list[i].process_name.ToString().Contains("报废");
                    var scrapType = true;
                    vaule = $@"{vaule} ('CL-DG','{list[i].job_num}','{list[i].part_no}', '{list[i].process_name}','{list[i].input_time}','{list[i].key1}','{list[i].lot_operator}','N','{list[i].input_time}','0',{(scrapType == true ? Convert.ToInt32(list[i].defect_value) : 0)}, {(scrapType == false ? Convert.ToInt32(list[i].defect_value) : 0)},'S001', '{(scrapType == false ? list[i].display_name : "")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'{((list.Count - 1 != i) ? ")," : ")")}";
                }
                //坏品信息存入报工表
                string insersql = $@"
                INSERT INTO labor_detail
                (COMPANY, JOBNUM, ASSEMBLYPARTNUM, PROCESSNAME, PAYROLLDATE, PRODUCTIONLOT_C, EMPLOYEENUM, STATUS, CREATE_TIME,LABORQTY, SCRAPQTY, DISCREPQTY, SCRAPREASONCODE,DISCRPRSNCODE,LABORNOTE)
                VALUES {vaule}";
                int Count = _db.GetConnection("SAE_OFN").Ado.ExecuteCommand(insersql.Replace(@"[\r\n]", ""));
                status = list.Count == Count ? true : false;
                //更改状态
                if (status)
                {
                    List<string> lot = data.AsEnumerable().Select(d => d.Field<string>("PRODUCTION_LOT_NO")).ToList();
                    List<string> process_name = data.AsEnumerable().Select(d => d.Field<string>("PROCESS_NAME")).ToList();
                    List<string> input_time = data.AsEnumerable().Select(d => d.Field<string>("INPUT_TIME")).ToList();
                    string strup = $@"
           	        UPDATE T_DEFECT_KEY_INFO SET STATUS =1 
                    WHERE PROCESS_NAME IN ('{string.Join("','", process_name)}')
                    AND KEY1 IN ('{string.Join("','", lot)}')
                    AND INPUT_TIME IN ('{string.Join("','", input_time)}') ";
                    int upcount = _db.GetConnection("SAE_OFN").Ado.ExecuteCommand(strup.Replace(@"[\r\n]", ""));
                }
            }
            return status;
        }
    }
}
